package com.renli.mapper;

import com.renli.pojo.JobPosition;
import com.renli.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class JobPositionMapper {
    /**
     * 添加职位
     *
     * @param jobPosition 职位信息
     * @return 添加是否成功
     * @throws SQLException 数据库操作异常
     */
    public static boolean add(JobPosition jobPosition) throws SQLException {
        String sql = "INSERT INTO tb_job_positions (position_name, department, description) VALUES (?, ?, ?)";
        Object[] params = {
                jobPosition.getPositionName(),
                jobPosition.getDepartment(),
                jobPosition.getDescription()
        };
        return JDBCUtils.executeUpdate(sql, params) > 0;
    }

    /**
     * 删除职位 by 职位ID
     *
     * @param positionId 职位ID
     * @return 删除是否成功
     * @throws SQLException 数据库操作异常
     */
    public static boolean delete(int positionId) throws SQLException {
        String sql = "DELETE FROM tb_job_positions WHERE position_id = ?";
        Object[] params = {positionId};
        return JDBCUtils.executeUpdate(sql, params) > 0;
    }

    /**
     * 更新职位信息
     *
     * @param jobPosition 职位信息
     * @return 更新是否成功
     * @throws SQLException 数据库操作异常
     */
    public static boolean update(JobPosition jobPosition) throws SQLException {
        String sql = "UPDATE tb_job_positions SET position_name=?, department=?, description=? WHERE position_id=?";
        Object[] params = {
                jobPosition.getPositionName(),
                jobPosition.getDepartment(),
                jobPosition.getDescription(),
                jobPosition.getPositionId()
        };
        return JDBCUtils.executeUpdate(sql, params) > 0;
    }

    /**
     * 根据职位ID获取职位信息
     *
     * @param positionId 职位ID
     * @return 职位信息
     * @throws SQLException 数据库操作异常
     */
    public static JobPosition getJobPositionById(int positionId) throws SQLException {
        String sql = "SELECT * FROM tb_job_positions WHERE position_id = ?";
        Object[] params = {positionId};
        ResultSet resultSet = JDBCUtils.executeQuery(sql, params);
        if (resultSet.next()) {
            return resultSetToJobPosition(resultSet);
        }
        return null; // 返回null表示未找到职位
    }

    /**
     * 获取所有职位的记录总数
     *
     * @return 记录总数
     * @throws SQLException 数据库操作异常
     */
    public static int getTotalJobPositionCount() throws SQLException {
        String sql = "SELECT COUNT(*) FROM tb_job_positions";
        ResultSet resultSet = JDBCUtils.executeQuery(sql);
        if (resultSet.next()) {
            return resultSet.getInt(1);
        }
        return 0;
    }

    /**
     * 获取所有职位列表
     *
     * @return 职位列表
     * @throws SQLException 数据库操作异常
     */
    public static List<JobPosition> getAllJobPositions() throws SQLException {
        List<JobPosition> jobPositions = new ArrayList<>();
        String sql = "SELECT * FROM tb_job_positions";
        ResultSet resultSet = JDBCUtils.executeQuery(sql);
        while (resultSet.next()) {
            jobPositions.add(resultSetToJobPosition(resultSet));
        }
        return jobPositions;
    }

    /**
     * 将ResultSet中的数据转换为JobPosition对象
     *
     * @param resultSet ResultSet对象
     * @return JobPosition对象
     * @throws SQLException 数据库操作异常
     */
    private static JobPosition resultSetToJobPosition(ResultSet resultSet) throws SQLException {
        JobPosition jobPosition = new JobPosition();
        jobPosition.setPositionId(resultSet.getInt("position_id"));
        jobPosition.setPositionName(resultSet.getString("position_name"));
        jobPosition.setDepartment(resultSet.getString("department"));
        jobPosition.setDescription(resultSet.getString("description"));
        return jobPosition;
    }

    /**
     * 分页模糊查询职位列表（根据职位名称模糊搜索）
     *
     * @param positionName 职位名称（可选，可以为null或空字符串）
     * @param pageNum      当前页码（从1开始）
     * @param pageSize     每页记录数
     * @return 职位列表
     * @throws SQLException 数据库操作异常
     */
    public static List<JobPosition> getJobPositionsByPage(String positionName, int pageNum, int pageSize) throws SQLException {
        List<JobPosition> jobPositions = new ArrayList<>();
        int startIndex = (pageNum - 1) * pageSize; // 计算起始索引
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM tb_job_positions");

        // 构建WHERE子句以实现模糊搜索
        if (positionName != null && !positionName.isEmpty()) {
            sqlBuilder.append(" WHERE position_name LIKE ?");
        }

        sqlBuilder.append(" LIMIT ?, ?");

        Object[] params;
        if (positionName != null && !positionName.isEmpty()) {
            params = new Object[]{"%" + positionName + "%", startIndex, pageSize};
        } else {
            params = new Object[]{startIndex, pageSize};
        }

        ResultSet resultSet = JDBCUtils.executeQuery(sqlBuilder.toString(), params);
        while (resultSet.next()) {
            jobPositions.add(resultSetToJobPosition(resultSet));
        }
        return jobPositions;
    }

    /**
     * 批量删除职位
     *
     * @param positionIds 职位ID列表
     * @return 删除是否成功
     * @throws SQLException 数据库操作异常
     */
    public static boolean batchDelete(List<Integer> positionIds) throws SQLException {
        if (positionIds.isEmpty()) {
            return false;
        }

        StringBuilder sql = new StringBuilder("DELETE FROM tb_job_positions WHERE position_id IN (");
        for (int i = 0; i < positionIds.size(); i++) {
            sql.append("?");
            if (i < positionIds.size() - 1) {
                sql.append(",");
            }
        }
        sql.append(")");

        Object[] params = positionIds.toArray();

        return JDBCUtils.executeUpdate(sql.toString(), params) > 0;
    }

}
